Conventional database programs process queries and other database commands. Many conventional database programs use a command language to allow a user or a program to supply commands to the database program, and the database program executes the command and may return a result. Many conventional database programs use a standard command language such as the Structured Query Language known as SQL. The SQL standard is specified by the American National Standards Institute, known as ANSI. One of the benefits of database programs that accept commands written in ANSI SQL or other standard command language is the ability of a command written for a database program provided by one supplier to be executed by a database program provided by a different supplier, provided only the ANSI-compliant commands are used.
Some database programs use a variation of SQL that differs from the ANSI standard. For example, a portion of the command may be the same as the ANSI SQL commands, but the format of the commands (e.g. the way parameters are specified) may be different from one database program to the next. Additionally, each database program may allow additional commands to be used that are not part of the standard command language. Thus, although some database programs speak a language that has much in common with other databases or a standard language, each may speak a variation of the language, referred to herein as a dialect. Commands written for one database program in one dialect will not operate in a database program that uses a different dialect.
It can be desirable to convert data from a format for use by one database program into the format for use by another database program. A new database program may contain desirable features that are not supported by the old database program. However, if the commands written for the old database program are not compatible with the commands that produce the same result in the new database program, the commands must be converted from a language and/or dialect of the old database program to a language and/or dialect of the new database program.
Although it is possible to write automated command converters to convert a command written for one database program to work on a different database program, because of the wide variety of data and commands and the potential for different interpretation of the same command by different database programs, it can be difficult to write a converter that will take into account every nuance of the command language of each database program. This task is made more difficult by the rapidly changing nature of some database commands, and commands that are either poorly documented or not documented at all, with users discovering these commands through word of mouth and trial and error techniques. Thus, the conversion process would require a manual comparison process, where the results of the command executed by the old database program would be compared against the results of the converted command executed by the new database programs.
Although it is also possible to write programs that compare the result of the command and the converted command executed by the old and the new database program, respectively, some differences between the result sets may be acceptable. Thus, even with the use of a converter, the results of the commands executed by the new database program and old database program, must be manually checked against one another to determine whether the command has been properly converted. If the results are different from the old database to the new (with the same data being used for comparison purposes), the user or programmer can make corrections to the converted command to provide the same result or a result that only contains acceptable differences, such as differences in the order of rows. Thus, even though the command may be automatically converted, the verification of the results remains a tedious and error-prone process.
If the commands are part of an application program, it can sometimes be faster and more accurate to simply rewrite the one or more commands in the language and dialect of the new database program from scratch. Therefore, although there has long been a need for automated converters, the practical application of automated converters that convert commands for use by one database program into commands that can be used by another database program have been limited because of lack of confidence in the converters requires a tedious and error-prone verification process.
Another problem with such converters is that the execution of a command for test purposes can alter the data in the database. Although the data from the database may be duplicated into a test version of the database, sufficient storage may not be available to allow the database to be duplicated, or the duplication process may itself be too time consuming to be repeatedly performed in the process of testing and correcting the converted commands. Thus, testing the converted commands on a duplicate copy of the data in the database may be impractical.
What is needed is a system and method that can convert one or more commands for use on one database program for use on one or more different database programs and ensure the results of the converted command are the same, or contain only acceptable differences, without requiring the data in the database to be altered upon completion of the command and without requiring a copy of the database to be used.